<?php
namespace app\service;
use think\facade\Db;
use app\model\TransferModel;
use app\model\ReceiptModel;
use app\model\SingleModel;
use Exception;

class ReceiptFlowService{  
    public function list($params){
        $where=[];
        $swhere=[];
        $page = $params['page'];
        $pageCount = $params['page_count'];
        //按出发日期查询
        if(!empty($params['start_date']) && !empty($params['end_date'])){
            if(!empty($params['start_date'])){
                $where[] = ['receipt_date','>= time',$params['start_date']];  
                $swhere[] = ['transfer_date','>= time',$params['start_date']]; 
            }
            if(!empty($params['end_date'])){
                $where[] = ['receipt_date','<= time',$params['end_date']];  
                $swhere[] = ['transfer_date','<= time',$params['end_date']];  
            }
        }

      
        if(!empty($params['subject_id'])){
            $where[] = ['subject_id','=',$params['subject_id']];  
            $swhere[] = ['to_bank','=',$params['subject_id']];  
        }        
        
        
        $transfer = TransferModel::alias('a')
        ->field('a.transfer_date as price_date,a.to_bank as subject_id,a.to_money as money,a.to_number as invoice_num,a.remark,a.id as client_id,a.sn,a.title,a.remark as client,c.bank_name as subject')      
        ->join('bank c','a.to_bank=c.id','left')
        ->where($swhere)->buildSql();

        $single = ReceiptModel::alias('a')
        ->field("a.receipt_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.client_id,b.sn,b.content as title,e.company_name as client,f.bank_name as subject")
        ->join('single b','a.income_id=b.id and a.type=b.type','left')        
        ->join('client e','b.client_id=e.id','left')
        ->join('bank f','a.subject_id=f.id','left')       
        ->where($where)
        ->buildSql();
        

        $receipt = ReceiptModel::alias('a')
        ->field("a.receipt_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.client_id,c.sn,c.title,e.company_name as client,f.bank_name as subject")
        ->join('sale_income b','a.income_id=b.id and a.type=b.type','left')
        ->join('sale c','b.pid=c.id','left')
        ->join('client e','b.client_id=e.id','left')
        ->join('bank f','a.subject_id=f.id','left')       
        ->where($where)
        ->buildSql();

        $data = Db::table($receipt.' a')->union([$single,$transfer])->page($page,$pageCount)->orderRaw("CAST(invoice_num as decimal)")->order('invoice_num','desc')->select();    
        
        $transferCount = TransferModel::alias('a')
            ->field('a.transfer_date as price_date,a.to_bank as subject_id,a.to_money as money,a.to_number as invoice_num,a.remark,a.id as client_id,a.sn,a.title,a.remark as client,c.bank_name as subject')      
            ->join('bank c','a.to_bank=c.id','left')
            ->where($swhere)->count();
        $singleCount = ReceiptModel::alias('a')
            ->field("a.receipt_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.client_id,b.sn,b.content as title,e.company_name as client,f.bank_name as subject")
            ->join('single b','a.income_id=b.id and a.type=b.type','left')         
            ->join('client e','b.client_id=e.id','left')
            ->join('bank f','a.subject_id=f.id','left')         
            ->where($where)->count(); 
        $receiptCount = ReceiptModel::alias('a')
            ->field("a.receipt_date as price_date,a.subject_id,a.money,a.invoice_num,a.remark,b.client_id,c.sn,c.title,e.company_name as client,f.bank_name as subject")
            ->join('sale_income b','a.income_id=b.id and a.type=b.type','left')
            ->join('sale c','b.pid=c.id','left')
            ->join('client e','b.client_id=e.id','left')
            ->join('bank f','a.subject_id=f.id','left')         
            ->where($where)->count(); 
        return ["list"=>$data,"count"=>$transferCount+$receiptCount+$singleCount];
    } 
}